package lingnan.cn.edu.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import lingnan.cn.edu.BaseAndImpl.StaffManageService;
import lingnan.cn.edu.User.Staffinf;
import lingnan.cn.edu.util.DbAccess;

public class StaffManageServiceImpl implements StaffManageService {

	@Override
	public List<Staffinf> query() {
		List<Staffinf> staffs = new ArrayList<Staffinf>();
		Connection conn = DbAccess.getConnection();
		String SQL = "select * from staff";		
		PreparedStatement prep = null;
		ResultSet rs=null;
		try {

			prep = conn.prepareStatement(SQL);
			rs = prep.executeQuery();
			while (rs.next()) {
				Staffinf staff = new Staffinf();
				staff.setStaffName(rs.getString("staffName"));
				staff.setPassword(rs.getString("password"));
				staff.setStaffSuper(rs.getInt("super"));
				staffs.add(staff);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep, rs);
		}

		return staffs;
	}

	@Override
	public List<Staffinf> findStaff(String staffname) {
		List<Staffinf> staffinfs = new ArrayList<Staffinf>();
		Connection conn = DbAccess.getConnection();
		String SQL = "select * from staff where staffName like ?";		
		PreparedStatement prep = null;
		ResultSet rs=null;
		try {
			prep = conn.prepareStatement(SQL);
			prep.setString(1, "%"+staffname+"%");
			rs = prep.executeQuery();
			while(rs.next()) {
				Staffinf staffinf = new Staffinf();
				staffinf.setStaffName(rs.getString("staffName"));
				staffinf.setPassword(rs.getString("password"));
				staffinf.setStaffSuper(rs.getInt("super"));
				staffinfs.add(staffinf);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DbAccess.release(conn, prep, rs);
		}
		return staffinfs;
	}

	@Override
	public int addStaff(Staffinf staffinf) {
		int flag = 0;
		Connection conn = DbAccess.getConnection();
		String SQL = "insert into staff values(?,?,?)";		
		PreparedStatement prep = null;
		try {
			prep = conn.prepareStatement(SQL);
			prep.setString(1,staffinf.getStaffName());
			prep.setString(2,staffinf.getPassword());
			prep.setInt(3,staffinf.getStaffSuper());			
			prep.executeUpdate();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep);
		}
		
		return flag;
	}

	@Override
	public int deleteByName(String staffname) {
		int flag = 0;
		Connection conn = DbAccess.getConnection();
		String SQL = "delete from staff where staffName = ?";		
		PreparedStatement prep = null;
		try {
			prep = conn.prepareStatement(SQL);
			prep.setString(1,staffname);			
			prep.executeUpdate();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep);
		}		
		return flag;
	}

	@Override
	public int updateStaff(Staffinf staffinf, String staffName) {
		int flag = 0;
		Connection conn = DbAccess.getConnection();
		String SQL = "update staff set staffName=?,password=?,super=? where staffName=?";		
		PreparedStatement prep = null;
		try {
			prep = conn.prepareStatement(SQL);
			prep.setString(1,staffinf.getStaffName());
			prep.setString(2,staffinf.getPassword());
			prep.setInt(3,staffinf.getStaffSuper());
			prep.setString(4,staffName);
			prep.executeUpdate();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbAccess.release(conn, prep);
		}
		
		return flag;
	}

}
